package nl.fw.yapool.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * A helper class that makes it easy to fire a query, get results and cleanup.
 * A typical usage scenario is the following (see also the test-classes): <pre>
 * DbConn c = new DbConn(myPool);
 * try {
 * 	c.setNQuery("select id from t where name like {@literal @name}");
 * 	c.nps.setString("name", "searchValue");
 * 	c.executeQuery();
 * 	... process results with c.rs ...
 * } catch (Exception e) {
 *   ... handle error ...
 * } finally { 
 * 	c.close(); 
 * }</pre>
 * or <pre>
 * DbConn c = new DbConn(myPool);
 * try {
 * 	// do queries
 *   c.commitAndClose();
 * } catch (Exception e) {
 *   c.rollbackAndClose(e);
 * }</pre> 
 * 
 * @author frederikw
 *
 */
public class DbConn {

	/** Logger used to log a warning when a close method encounters an error. */ 
	public static Logger closeLogger = LoggerFactory.getLogger(DbConn.class);

	/** Database pool to acquire and release a connection. */
	public SqlPool pool;
	/** 
	 * Query cache to use with database pool (cached statements are not closed by this class). 
	 * Note that a query cache for statements with auto-generated keys will throw an SQLException.
	 */
	public IQueryCache qc;
	
	/** 
	 * Query builder used to create (named) prepared statements. 
	 * Note that a query builder for statements with auto-generated keys will throw an SQLException.
	 */
	public IQueryBuilder qb;

	/** Connection factory to get and close a connection. Used when {@link #pool} is null. */
	public SqlFactory connFactory;
	/** An actual database connection, provided by implementation or set when {@link #getConnection()} is called. */
	public Connection conn;
	/** A (non-cached) statement, set when {@link #setStatement()} is called. */
	public Statement st;
	/** A prepared statement, set when {@link #setQuery(String)} is called. */
	public PreparedStatement ps;
	/** A named prepared statement, set when {@link #setNQuery(String)} is called. */
	public NamedParameterStatement nps;
	/** A resultset, used as placeholder for the query results of a query. */
	public ResultSet rs;
	
	/** Does nothing. */
	public DbConn() { super(); }
	
	/** Sets {@link #pool} to given pool. */
	public DbConn(final SqlPool pool) {
		this.pool = pool;
	}
	
	/** Sets {@link #pool} to given pool with prepared statement cache for connections. */
	public DbConn(final SqlPool pool, IQueryCache qc) {
		this.pool = pool;
		this.qc = qc;
	}

	/** Sets {@link #pool} to given pool with prepared statement cache for connections. */
	public DbConn(final SqlPool pool, IQueryBuilder qb) {
		this.pool = pool;
		this.qb = qb;
	}

	/** Sets {@link #connFactory} to the given connection factory. */
	public DbConn(final SqlFactory connFactory) {
		this.connFactory = connFactory;
	}

	/** Sets {@link #conn} to the given connection. */
	public DbConn(final Connection conn) {
		this.conn = conn;
	}

	/** 
	 * Acquires a connection from the {@link #pool} or {@link #connFactory}, but only when {@link #conn} is null. 
	 * Sets {@link #conn} to the new connection.
	 */
	public Connection getConnection() {
		
		if (conn == null) {
			if (pool != null) {
				conn = pool.acquire();
			} else if (connFactory != null) {
				conn = connFactory.create();
			} else {
				throw new IllegalStateException("Database connection not set and there is no database pool or connection factory available to get a new database connection.");
			}
		}
		return conn;
	}

	/** 
	 * Sets {@link #st}, closes previous query and gets a connection if needed.
	 * <br>See also {@link #execute(String)}. 
	 */
	public Statement setStatement() throws SQLException {
		return setStatement(null, -1);
	}

	/** Calls {@link #setQuery(String, int)} with autoGeneratedKeys ignored. */
	public PreparedStatement setQuery(final String query) throws SQLException {
		return (PreparedStatement) setStatement(query, -1);
	}
	
	/** 
	 * Sets {@link #ps} with the given query. Closes previous query and gets a connection if needed. 
	 * <br>If autoGeneratedKeys is -1, it is ignored, else if autoGeneratedKeys
	 * equals java.sql.Statement.RETURN_GENERATED_KEYS for example, 
	 * generated keys are returned in the resultset
	 * (how generated keys are returned depends on the type of database,
	 * e.g. mysql will return a column with the name "GENERATED_KEY").
	 * @throws SQLException if {@link #qc} if set and RETURN_GENERATED_KEYS is passed,
	 * a "not supported" exception is thrown. In this case, configure the query cache to recognize
	 * which statements generate keys (see for example {@link SimpleQueryBuilder#addGeneratesKeys(String, boolean)}.
	 */
	public PreparedStatement setQuery(final String query, final int autoGeneratedKeys) throws SQLException {
		return (PreparedStatement) setStatement(query, autoGeneratedKeys);
	}
	
	/**
	 * Sets {@link #st} if query is null, else sets {@link #ps}.
	 * Closes previous query and gets a connection if needed. 
	 */
	protected Statement setStatement(final String query, final int autoGeneratedKeys) throws SQLException {

		closeQuery();
		getConnection();
		if (query == null) {
			st = conn.createStatement();
			return st;
		}
		if (autoGeneratedKeys > -1) {
			if (qc != null) {
				throw new SQLException("Query with auto-generated keys not supported when query-cache is used.");
			}
			ps = conn.prepareStatement(query, autoGeneratedKeys);
		} else { 
			if (qc != null) {
				ps = qc.getQuery(conn, query);
			} else if (qb != null) {
				ps = qb.createQuery(conn, query);
			} else {
				ps = conn.prepareStatement(query);
			}
		}
		return ps;
	}


	/** Calls {@link #setNQuery(String, int)} with autoGeneratedKeys ignored. */
	public NamedParameterStatement setNQuery(final String query) throws SQLException {
		return setNQuery(query, -1);
	}
	/** 
	 * Same as {@link #setQuery(String, int)}, but this time for a named query (sets {@link #nps} instead of {@link #ps}). 
	 */
	public NamedParameterStatement setNQuery(final String query, final int autoGeneratedKeys) throws SQLException {

		closeQuery();
		getConnection();
		if (autoGeneratedKeys > -1) { 
			if (qc != null) {
				throw new RuntimeException("Query with auto-generated keys not supported when query-cache is used.");
			}
			nps = new NamedParameterStatement(conn, query, autoGeneratedKeys);
		} else { 
			if (qc != null) {
				nps = qc.getNamedQuery(conn, query);
			} else if (qb != null) {
				nps = qb.createNamedQuery(conn, query);
			} else {
				nps = new NamedParameterStatement(conn, query);
			}
		}
		return nps;
	}
	
	/**
	 * Execute {@link #st} statement with given query.
	 * <br> If {@link #qb} is set, tries to lookup the query-SQL with the given query as query-name.
	 * <br>Calls {@link #setStatement()} if needed, never uses {@link #qc}.
	 * @return update count or -1 if there is no result or when a result-set is available and set to {@link #rs} .
	 */
	public int execute(String query) throws SQLException {
		
		if (st == null) {
			setStatement();
		}
		int updateCount = -1;
		String qbQuery = (qb == null ? null : qb.getQuerySql(query));
		boolean haveRs = st.execute(qbQuery == null ? query : qbQuery);
		if (haveRs) {
			rs = st.getResultSet();
		} else {
			updateCount = st.getUpdateCount();
		}
		return updateCount;
	}
	
	/**
	 * Executes a query set in {@link #ps} or {@link #nps}.
	 * @return the resultset from the query, also set in {@link #rs}
	 */
	public ResultSet executeQuery() throws SQLException {
		
		if (ps != null) {
			rs = ps.executeQuery();
		} else if (nps != null) {
			rs = nps.executeQuery();
		}
		return rs;
	}
	
	/**
	 * Calls {@link #executeUpdate(boolean)} with "fetch generated keys" set to true.
	 * @return number of records updated
	 */
	public int executeUpdate() throws SQLException {
		return executeUpdate(true);
	}
	
	/**
	 * Executes an insert/update query set in {@link #ps} or {@link #nps} and sets any generated keys in {@link #rs}
	 * if parameter "fetchGeneratedKeys" is true ({@link #rs} will be empty if there are no auto-generated keys).
	 * @param fetchGeneratedKeys if true, {@link #rs} is set to the generated keys. Note that some database drivers
	 * (like MySQL) will throw an exception if the insert/update statement is not created 
	 * with the {@link Statement#RETURN_GENERATED_KEYS} option but generated keys are fetched in this method.  
	 * @return number of records updated
	 */
	public int executeUpdate(boolean fetchGeneratedKeys) throws SQLException {
		
		int updateCount = -1;
		if (ps != null) {
			updateCount = ps.executeUpdate();
			if (fetchGeneratedKeys) {
				rs = ps.getGeneratedKeys();
			}
		} else if (nps != null) {
			updateCount = nps.executeUpdate();
			if (fetchGeneratedKeys) {
				rs = nps.getStatement().getGeneratedKeys();
			}
		}
		return updateCount;
	}

	
	/** 
	 * Closes {@link #rs} and {@link #st} if set.
	 * Only closes {@link #ps} and/or {@link #nps} if they are not cached ({@link #qc} is <code>null</code> or <code>qc.isCached</code> returns false). 
	 * Does not release or close the database connection.
	 * Uses the {@link #closeLogger} to log errors as warnings.
	 * <br>Sets rs, st, ps and nps to null so that subsequent calls to this method or {@link #close()} have no effect. 
	 * <br>This is required in for example Tomcat, see for example the "Random Connection Closed Exceptions" problem
	 * described at http://yzb.hit.edu.cn/docs/printer/jndi-datasource-examples-howto.html#Common%20Problems
	 */
	public void closeQuery() {
		
		if (rs != null) { 
			close(rs); 
			rs = null; 
		}
		if (st != null) { 
			close(st); 
			st = null; 
		}
		if (ps != null) { 
			if (qc == null) {
				close(ps); 
			} else {
				qc.close(ps);
			}
			ps = null; 
		}
		if (nps != null) { 
			if (qc == null) { 
				close(nps); 
			} else {
				qc.close(nps);
			}
			nps = null; 
		}
	}
	
	/**
	 * Commits the transaction if a connection is open and calls {@link #close()}.
	 * @throws SQLException if commit fails.
	 */
	public void commitAndClose() throws SQLException {
		
		if (conn != null) {
			conn.commit();
		}
		close();
	}
	
	/**
	 * Rolls back the transaction if a connection is open and calls {@link #close()}.
	 */
	public void rollbackAndClose() {
		rollbackAndClose(null);
	}
	
	/**
	 * Rolls back the transaction if a connection is open and calls {@link #close()}.
	 * @param t if not null, this method will always throw a runtime exception
	 * after rollback and close.
	 */
	public void rollbackAndClose(Throwable t) {
		
		if (conn == null) {
			closeLogger.debug("Nothing to rollback, no connection is open.");
		} else {
			try {
				conn.rollback();
			} catch (Exception re) {
				closeLogger.warn("Failed to rollback transaction: " + re);
			}
		}
		close();
		rethrowRuntime(t);
	}
	
	/**
	 * Throws a runtime-exception if given exception is not null.
	 * @param t if an instance of a runtime-exception, throws the runtime-exception, 
	 * else throws a runtime-exception with information copied from checked exception.
	 */
	public static void rethrowRuntime(Throwable t) {
		
		if (t == null) {
			return;
		}
		if (t instanceof Error) {
			throw ((Error)t);
		}
		if (t instanceof RuntimeException) {
			throw ((RuntimeException)t);
		}
		RuntimeException re = new RuntimeException(t.toString(), t.getCause());
		re.setStackTrace(t.getStackTrace());
		throw re;
	}

	/**   
	 * Calls {@link #closeQuery()} and, if there is a pool, releases the database connection 
	 * or closes the database connection.  
	 */
	public void close() {

		closeQuery();
		if (conn != null) {
			if (pool != null) {
				pool.release(conn);
			} else if (connFactory != null) {
				connFactory.destroy(conn);
			} else {
				close(conn);
			}
			conn = null;
		}
	}
	
	/** Closes a statement (checks for null-value), logs any error as warning using closeLogger. */
	public static void close(final Statement s) {
		
		try {
			if (s != null) s.close();
		} catch (SQLException se) {
			closeLogger.warn("Failed to close statement " + s + ": " + se);
		}
	}

	/** Closes a statement (checks for null-value), logs any error as warning using closeLogger. */
	public static void close(final NamedParameterStatement s) {
		
		try {
			if (s != null) s.close();
		} catch (SQLException se) {
			closeLogger.warn("Failed to close named statement " + s + ": " + se);
		}
	}
	
	/** Closes a resultset (checks for null-value), logs any error as warning using closeLogger. */
	public static void close(final ResultSet rs) {
		
		try {
			if (rs != null) rs.close();
		} catch (SQLException se) {
			closeLogger.warn("Failed to close result set " + rs + ": " + se);
		}
	}

	/** Closes a database Connection (checks for null-value), logs any error as warning using closeLogger. */
	public static void close(final Connection c) {
		
		try {
			if (c != null) c.close();
		} catch (SQLException se) {
			closeLogger.warn("Failed to close database connection " + c + ": " + se);
		}
	}

	/**
	 * Utility method for constructing a prepared statement using the 'in' keyword.
	 * <br>Copied from http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives.
	 * <br>Usage:<pre>
	 * String SQL_FIND = "SELECT id, name, value FROM data WHERE id IN (%s)" 
	 * String sql = String.format(SQL_FIND, preparePlaceHolders(ids.size()));
	 * statement = connection.prepareStatement(sql);
	 * setValues(statement, ids.toArray());
	 * resultSet = statement.executeQuery();
	 * </pre>
	 * See also {@link #setValues(PreparedStatement, Object...)}.
	 */
	public static String preparePlaceHolders(final int length) {
	    
		StringBuilder sb = new StringBuilder();
	    for (int i = 0; i < length;) {
	        sb.append("?");
	        if (++i < length) {
	            sb.append(",");
	        }
	    }
	    return sb.toString();
	}

	/** 
	 * See comments on {@link #preparePlaceHolders(int)}
	 */
	public static void setValues(final PreparedStatement preparedStatement, final Object... values) throws SQLException {
	    
		for (int i = 0; i < values.length; i++) {
	        preparedStatement.setObject(i + 1, values[i]);
	    }
	}
	
	/** 
	 * See comments on {@link #preparePlaceHolders(int)}
	 */
	public static void setValues(final PreparedStatement preparedStatement, final Collection<?> values) throws SQLException {
	    
		int i = 0;
		for (Object o : values) {
	        preparedStatement.setObject(++i, o);
		}
	}

}
